package com.hngm.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hngm.bean.About;
import com.hngm.bean.Product;
import com.hngm.bean.Productpicture;
import com.hngm.util.DBManager;
import com.hngm.util.QueryDataResult;
import com.hngm.util.StringUtil;

public class ProductDao {
	/**
	 * 根据产品类型查询对应的产品列表
	 */
	public List<Product> queryProductListByType(String pType) {
		List<Product> pList = new ArrayList<Product>();
		// 定义sql语句
		String sql = "select * from product where producttypetable_id = " + pType;
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				String materail = rs.getString("materail");
				String saleprice = rs.getString("saleprice");
				String marketprice = rs.getString("marketprice");
				String brand = rs.getString("brand");
				String model = rs.getString("model");
				String style = rs.getString("style");
				int pack = rs.getInt("pack");
				String color = rs.getString("color");
				String stylepositioning = rs.getString("stylepositioning");
				int customized = rs.getInt("customized");
				String elements = rs.getString("elements");
				long producttypetable_id = rs.getLong("producttypetable_id");
				String mainpic = rs.getString("mainpic");
				String rotationpic = rs.getString("rotationpic");
				int rotation = rs.getInt("rotation");
				int recommend = rs.getInt("recommend");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Product p = new Product(id, name, materail, saleprice, marketprice, brand, model, style, pack, color,
						stylepositioning, customized, elements, producttypetable_id, mainpic, rotationpic, rotation,
						recommend, creationtime, updatetime, null);
				pList.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return pList;
	}

	// 根据产品ID查询产品详情的方法
	public Product queryProductInfoById(String pid) {
		Product p = new Product();
		// 定义sql语句
		String sql = "select * from product where id = " + pid;
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				String materail = rs.getString("materail");
				String saleprice = rs.getString("saleprice");
				String marketprice = rs.getString("marketprice");
				String brand = rs.getString("brand");
				String model = rs.getString("model");
				String style = rs.getString("style");
				int pack = rs.getInt("pack");
				String color = rs.getString("color");
				String stylepositioning = rs.getString("stylepositioning");
				int customized = rs.getInt("customized");
				String elements = rs.getString("elements");
				long producttypetable_id = rs.getLong("producttypetable_id");
				String mainpic = rs.getString("mainpic");
				String rotationpic = rs.getString("rotationpic");
				int rotation = rs.getInt("rotation");
				int recommend = rs.getInt("recommend");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				p = new Product(id, name, materail, saleprice, marketprice, brand, model, style, pack, color,
						stylepositioning, customized, elements, producttypetable_id, mainpic, rotationpic, rotation,
						recommend, creationtime, updatetime, null);
				// 根据产品id查询产品的图片列表的方法
				List<Productpicture> picList = queryPicttureListByPid(pid);
				p.setPicList(picList);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return p;
	}

	// 根据产品id查询产品的图片列表的方法
	public List<Productpicture> queryPicttureListByPid(String pid) {
		List<Productpicture> picList = new ArrayList<Productpicture>();
		// 根据产品详情查询产品ID
		String sql = "select * from productpicture where product_id = " + pid;

		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String picurl = rs.getString("picurl");
				long product_id = rs.getLong("product_id");
				int del = rs.getInt("del");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Productpicture pic = new Productpicture(id, picurl, product_id, del, creationtime, updatetime);
				picList.add(pic);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return picList;
	}

	// 是否上推荐
	public List<Product> queryProductByRec() {
		List<Product> prList = new ArrayList<Product>();
		// 定义查询sql语句
		String sql = "select * from product where recommend = 1";
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				String materail = rs.getString("materail");
				String saleprice = rs.getString("saleprice");
				String marketprice = rs.getString("marketprice");
				String brand = rs.getString("brand");
				String model = rs.getString("model");
				String style = rs.getString("style");
				int pack = rs.getInt("pack");
				String color = rs.getString("color");
				String stylepositioning = rs.getString("stylepositioning");
				int customized = rs.getInt("customized");
				String elements = rs.getString("elements");
				long producttypetable_id = rs.getLong("producttypetable_id");
				String mainpic = rs.getString("mainpic");
				String rotationpic = rs.getString("rotationpic");
				int rotation = rs.getInt("rotation");
				int recommend = rs.getInt("recommend");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Product p = new Product(id, name, materail, saleprice, marketprice, brand, model, style, pack, color,
						stylepositioning, customized, elements, producttypetable_id, mainpic, rotationpic, rotation,
						recommend, creationtime, updatetime, null);
				prList.add(p);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return prList;
	}

	// 是否上轮播
	public List<Product> queryProductByRot() {

		List<Product> proList = new ArrayList<Product>();
		// 定义查询sql语句
		String sql = "select * from product where rotation = 1";
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sql);
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				String materail = rs.getString("materail");
				String saleprice = rs.getString("saleprice");
				String marketprice = rs.getString("marketprice");
				String brand = rs.getString("brand");
				String model = rs.getString("model");
				String style = rs.getString("style");
				int pack = rs.getInt("pack");
				String color = rs.getString("color");
				String stylepositioning = rs.getString("stylepositioning");
				int customized = rs.getInt("customized");
				String elements = rs.getString("elements");
				long producttypetable_id = rs.getLong("producttypetable_id");
				String mainpic = rs.getString("mainpic");
				String rotationpic = rs.getString("rotationpic");
				int rotation = rs.getInt("rotation");
				int recommend = rs.getInt("recommend");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Product p = new Product(id, name, materail, saleprice, marketprice, brand, model, style, pack, color,
						stylepositioning, customized, elements, producttypetable_id, mainpic, rotationpic, rotation,
						recommend, creationtime, updatetime, null);
				proList.add(p);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return proList;

	}

	/**
	 * 根据满足条件得产品列表
	 */

	public ArrayList<Product> queryProductListByParm(String productname, String beginprice, String endprice, String del,
			String orderby, int beginIndex, int limit) {
		ArrayList<Product> pList = new ArrayList<Product>();
		// 定义sql语句
		// String sql = String.format("select * from product limit %d , %d",
		// beginIndex,limit);
		// 将传过来的查询条件作为where条件：拼接到sql语句中
		StringBuffer sb = new StringBuffer("select * from product where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(productname)) {
			// sb.append(String.format(" and name like '%s%s%s'",
			// "%",productname,"%"));
			sb.append(String.format(" and name like '%s'", "%" + productname + "%"));
		}
		if (StringUtil.isNotNullorEmpty(beginprice)) {
			sb.append(String.format(" and saleprice > '%s'", beginprice));
		}
		if (StringUtil.isNotNullorEmpty(endprice)) {
			sb.append(String.format(" and saleprice < '%s'", endprice));
		}
		if (StringUtil.isNotNullorEmpty(del)) {
			sb.append(String.format(" and del = '%s'", del));
		}
		if (StringUtil.isNotNullorEmpty(orderby)) {
			if ("priceasc".equals(orderby)) {
				sb.append(" order by saleprice asc ");
			} else if ("pricedesc".equals(orderby)) {
				sb.append(" order by saleprice desc ");
			}
		}
		sb.append(String.format(" limit %d,%d ", beginIndex, limit));
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				String materail = rs.getString("materail");
				String saleprice = rs.getString("saleprice");
				String marketprice = rs.getString("marketprice");
				String brand = rs.getString("brand");
				String model = rs.getString("model");
				String style = rs.getString("style");
				int pack = rs.getInt("pack");
				String color = rs.getString("color");
				String stylepositioning = rs.getString("stylepositioning");
				int customized = rs.getInt("customized");
				String elements = rs.getString("elements");
				long producttypetable_id = rs.getLong("producttypetable_id");
				String mainpic = rs.getString("mainpic");
				String rotationpic = rs.getString("rotationpic");
				int rotation = rs.getInt("rotation");
				int recommend = rs.getInt("recommend");
				String creationtime = rs.getString("creationtime");
				String updatetime = rs.getString("updatetime");
				Product p = new Product(id, name, materail, saleprice, marketprice, brand, model, style, pack, color,
						stylepositioning, customized, elements, producttypetable_id, mainpic, rotationpic, rotation,
						recommend, creationtime, updatetime, null);
				pList.add(p);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return pList;
	}

	public int queyrProductListByParamCount(String productname, String beginprice, String endprice, String del,
			String orderby) {
		// 定义sql语句
//		String sql = "select count(1) as count from product";
		// 将传过来的查询条件作为where条件：拼接到sql语句中
		StringBuffer sb = new StringBuffer("select count(1) as count from product where 1=1");

		// 拼接查询条件
		if (StringUtil.isNotNullorEmpty(productname)) {
			// sb.append(String.format(" and name like '%s%s%s'",
			// "%",productname,"%"));
			sb.append(String.format(" and name like '%s'", "%" + productname + "%"));
		}
		if (StringUtil.isNotNullorEmpty(beginprice)) {
			sb.append(String.format(" and saleprice > '%s'", beginprice));
		}
		if (StringUtil.isNotNullorEmpty(endprice)) {
			sb.append(String.format(" and saleprice < '%s'", endprice));
		}
		if (StringUtil.isNotNullorEmpty(del)) {
			sb.append(String.format(" and del = '%s'", del));
		}
		// 调用DBManager
		QueryDataResult qdr = DBManager.executeQuery(sb.toString());
		// 在qdr中获取resultSet结果集
		ResultSet rs = qdr.getRs();

		try {
			// 遍历rs结果集
			while (rs.next()) {
				return rs.getInt("count");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBManager.closedAll(qdr.getConn(), qdr.getSt(), qdr.getRs());
		}
		return 0;
	}
   /**
    * 插入语句的方法
    * */
	public int insertProduct(Product p) {
		//定义插入sql语句
		String sql = String.format("insert into product(name,materail,saleprice,"
				+ "marketprice,brand,model,style,pack,color,stylepositioning,"
				+ "customized,elements,producttypetable_id,mainpic,"
				+ "rotationpic,rotation,recommend) values ('%s','%s',"
				+ "'%s','%s','%s','%s','%s',%d,'%s','%s',%d,'%s',%d,"
				+ "'%s','%s',%d,%d)", 
				p.getName(),
				p.getMaterail(),
				p.getSaleprice(),
				p.getMarketprice(),
				p.getBrand(),
				p.getModel(),
				p.getStyle(),
				p.getPack(),
				p.getColor(),
				p.getStylepositioning(),
				p.getCustomized(),
				p.getElements(),
				p.getProducttypetable_id(),
				p.getMainpic(),
				p.getRotationpic(),
				p.getRotation(),
				p.getRecommend()
				);
		return DBManager.executeUpdate(sql);
	}

public int delProduct(String productId) {
	//定义删除产品的sql语句
	String sql = "delete from product where id = " + productId;
	return DBManager.executeUpdate(sql);
}
/**
 * 编辑产品
 * */
public int editProduct(Product p) {
	String sql = String.format("update product set "
			+ "name='%s',"
			+ "materail='%s',"
			+ "saleprice='%s',"
			+ "marketprice='%s',"
			+ "brand='%s',"
			+ "model='%s',"
			+ "style='%s',"
			+ "pack=%d,"
			+ "color='%s',"
			+ "stylepositioning='%s',"
			+ "customized=%d,"
			+ "elements='%s',"
			+ "producttypetable_id=%d,"
			+ "mainpic='%s',"
			+ "rotationpic='%s',"
			+ "rotation=%d,"
			+ "recommend=%d "
			+ "where id=%d",
			p.getName(),
			p.getMaterail(),
			p.getSaleprice(),
			p.getMarketprice(),
			p.getBrand(),
			p.getModel(),
			p.getStyle(),
			p.getPack(),
			p.getColor(),
			p.getStylepositioning(),
			p.getCustomized(),
			p.getElements(),
			p.getProducttypetable_id(),
			p.getMainpic(),
			p.getRotationpic(),
			p.getRotation(),
			p.getRecommend(),
			p.getId());
	return DBManager.executeUpdate(sql);
}
}
